package com.springcloud.framework.servicea.dao;

import com.springcloud.framework.servicea.entity.UserEntity;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.transaction.annotation.Transactional;

import java.util.Date;
import java.util.List;

public interface UserRepository extends JpaRepository<UserEntity, Integer> {

    List<UserEntity> findByNameLike(String name);

    List<UserEntity> findByNameAndAndDeptId(String name, Integer deptId);

    List<UserEntity> findByCreateTimeGreaterThanAndCreateTimeLessThan(Date startDate, Date endDate);

    //原生SQl使用，Param注解
    @Query(value = "select * from t_user where dept_id=:deptId and name=:username", nativeQuery = true)
    List<UserEntity> selectByDeptIdAndName(@Param("deptId") int deptId, @Param("username") String name);

    @Query(value = "select * from t_user where dept_id=:deptId and sex=:sex", nativeQuery = true)
    Page<UserEntity> selectByDeptIdAndSex(@Param("deptId") String deptId, @Param("sex") String sex, Pageable pageable);


    @Transactional
    @Modifying
    @Query(value = "update t_user set name=:name where id=:id", nativeQuery = true)
    int updateNameById(@Param("id") Integer id, @Param("name") String name);

    //自定义删除
    @Transactional
    int deleteBySex(Integer sex);


    @Query(value = "select * from t_user where if(?1 IS NOT NULL,name like  CONCAT('%',?1,'%'),1=1) " +
            "and if(?2 IS NOT NULL,sex=?2,1=1)",nativeQuery = true)
    List<UserEntity> selectListByNameLike(String name,Integer sex);

    @Query(value = "select * from t_user where if(:name IS NOT NULL,name like  CONCAT('%',:name,'%'),1=1) " +
            "and if(:sex IS NOT NULL,sex=:sex,1=1)",nativeQuery = true)
    List<UserEntity> selectListByNameLike2(@Param("name") String name,@Param("sex") Integer sex);
}
